using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Drawing;
using System.IO;

namespace SQLDBDAL
{
    public class SQLDBHelper
    {
        public static readonly string CONN_STRING = ConfigurationManager.ConnectionStrings["AALConnectionString"].ConnectionString;

        // Execute a SqlCommand (that returns no resultset) against the database specified in the CONNECTION STRING 
        public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {

            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(connString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        // Execute a SqlCommand (that returns no resultset) against an EXISTING DATABASE CONNECTION 
        public static int ExecuteNonQuery(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {

            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        // Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction
        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        // Execute a SqlCommand that returns a resultset against the database specified in the connection string
        public static SqlDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connString);

            // we use a try/catch here because if the method throws an exception we want to 
            // close the connection throw code, because no datareader will exist, hence the 
            // commandBehaviour.CloseConnection will not work
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                //cmd.Parameters.Clear();
                return rdr;
            }
            catch (Exception ex)
            {
                conn.Close();
                throw ex;
            }
        }

        // Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string 
        public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(connString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        // Execute a SqlCommand that returns the first column of the first record against an existing database connection 
        public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {

            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

        // Get new IDENTITY value and close the current connection
        public static void SetNewIdentity(SqlConnection conn, int objID )
        {            
            string SQL_SELECT_IDENTITY_PARM = "SELECT @@IDENTITY";
            object obj = ExecuteScalar(conn, CommandType.Text, SQL_SELECT_IDENTITY_PARM, null);
            if (obj != null)
            {
                objID = int.Parse(obj.ToString());
            }
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }

        public static int SetNewIdentity2(SqlConnection conn, int objID)
        {
            int id = 0;
            string SQL_SELECT_IDENTITY_PARM = "SELECT @@IDENTITY";
            object obj = ExecuteScalar(conn, CommandType.Text, SQL_SELECT_IDENTITY_PARM, null);
            if (obj != null)
            {
                objID = int.Parse(obj.ToString());
                id = objID;
            }
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
            return id;
        }

        // convert Image into bytes[]
        public static byte[] GetBytesFromImage(Image img)
        {
            
            byte[] bytes;
            MemoryStream ms = new MemoryStream();

            img.Save(ms, img.RawFormat);

            bytes = ms.GetBuffer();

            return bytes;
        }

        // Prepare a command for execution
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        //Convert from Float to Star =))
        public static string GetStar(int starNumber)
        {
            string result = "";
            switch (starNumber)
            {
                case 0:
                    result = "~/CoreLayout/Images/Stars/0-star.png";
                    break;
                case 1:
                    result = "~/CoreLayout/Images/Stars/1-star.png";
                    break;
                case 2:
                    result = "~/CoreLayout/Images/Stars/2-stars.png";
                    break;
                case 3:
                    result = "~/CoreLayout/Images/Stars/3-stars.png";
                    break;
                case 4:
                    result = "~/CoreLayout/Images/Stars/4-stars.png";
                    break;
                case 5:
                    result = "~/CoreLayout/Images/Stars/5-stars.png";
                    break;
            }
            return result;
        }

    }
}
